package com.eryos.android.cigarettecounter.database;

/**
 * Project :  CigaretteCounter
 * Author :   Olivier Combe 
 * URL :      http://eryos.fr/CigaretteCounter/
 *
 * Copyright (C) 2011 - Olivier Combe 
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 */

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.TimeZone;

import android.content.Context;
import android.database.Cursor;
import android.database.CursorIndexOutOfBoundsException;

import com.eryos.android.cigarettecounter.beans.SmokeAverage;
import com.eryos.android.cigarettecounter.beans.SmokeCounter;
import com.eryos.android.cigarettecounter.beans.SmokeTime;


public class CigaretteDatabase extends DBAdapter {

	private static final String CSV_SEPARATOR = ";";
	
	public CigaretteDatabase(Context ctx) {
		super(ctx);
	}


	public static final int NUMBER_OF_HISTORY = 6;	
	public static final int ROUNDING = 2;	

	/*****************************************************************************/
	/*****************************************************************************/
	/*****************************************************************************/


	/*
	 * Return the result of the division between SUM and NB
	 */
	public double getAverage(double _sum, double _nb){

		try {
			BigDecimal sum = new BigDecimal(_sum).setScale(2);
			BigDecimal nb  = new BigDecimal(_nb).setScale(2);

			return sum.divide(nb).setScale(2, RoundingMode.DOWN).doubleValue();
		} catch (Exception e) {

			e.printStackTrace();

			return -1;
		}		
	}


	/*
	 * Parse an SQL cursor into a ArrayList of SmokeAverage(Label/Double) 
	 */
	public ArrayList<SmokeAverage> parseCursorAsSmokeAverage( Cursor mCursor ){

		ArrayList<SmokeAverage> res = new ArrayList<SmokeAverage>();

		if (mCursor != null){
			mCursor.moveToFirst();
			for(int i=0; i < mCursor.getCount(); i++) {			
				res.add(new SmokeAverage(mCursor.getString(0), mCursor.getDouble(3) ));
				mCursor.moveToNext();
			}
		}

		return res;
	}

	/*
	 * Parse an SQL cursor into a ArrayList of SmokeCount(Label/Int) 
	 */
	public ArrayList<SmokeCounter> parseCursorAsSmokeCounter( Cursor mCursor ){

		ArrayList<SmokeCounter> res = new ArrayList<SmokeCounter>();

		if (mCursor != null){
			mCursor.moveToFirst();
			for(int i=0; i < mCursor.getCount(); i++) {
				SmokeCounter smoke = new SmokeCounter(mCursor.getString(0),mCursor.getInt(1));

				try {
					Calendar c = Calendar.getInstance();
					c.set(Calendar.DAY_OF_MONTH, mCursor.getInt(2));
					c.set(Calendar.MONTH, mCursor.getInt(3));
					c.set(Calendar.YEAR, mCursor.getInt(4));
					smoke.setDate(c);
				} catch (Exception e) {
					e.printStackTrace();
				}

				res.add(smoke);
				mCursor.moveToNext();
			}
		}

		return res;
	}


	/*****************************************************************************/
	/*****************************************************************************/
	/*****************************************************************************/


	/**
	 * Return the number of cigarettes smoked this day
	 */
	public int getDailyCount(){
		String sqlQuery = 	
			"SELECT   strftime('%d/%m/%Y', smoke_time), count(1) "+
			"FROM     "+ DATABASE_TABLE +" "+
			"WHERE    strftime('%d/%m/%Y', smoke_time) = strftime('%d/%m/%Y', 'now') "+
			"GROUP BY strftime('%d/%m/%Y', smoke_time) ;";

		int res = 0;

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
			try {
				res = mCursor.getInt(1);
			} catch (CursorIndexOutOfBoundsException e) {
				e.printStackTrace();
			}
		}

		return res;
	}


	/**
	 * Return the number of cigarettes smoked this week
	 */
	public int getWeeklyCount(){
		String sqlQuery = 
			"SELECT   count(1) Nb "+
			"FROM     "+ DATABASE_TABLE +" "+
			"WHERE    strftime('%W', smoke_time) = strftime('%W', 'now'); ";

		int res = -1;

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
			res = mCursor.getInt(0);
		}

		return res;
	}
	
	/**
	 * Return the number of cigarettes smoked this week
	 */
	public double getWeeklyCost(){

		String sqlQuery = 
			"SELECT   sum(ifnull(smoke_valid,0)) Nb "+
			"FROM     "+ DATABASE_TABLE +" "+
			"WHERE    strftime('%W', smoke_time) = strftime('%W', 'now'); ";

		double res = -1;

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
			res = mCursor.getDouble(0);
		}

		return res;
	}


	/**
	 * Return the average number of cigarettes smoked by day this week
	 */
	public double getWeeklyAverage(){
		try {
			String sqlQuery = 
				"SELECT sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
				"FROM  "+
				"( "+
				"    SELECT   strftime('%d/%m/%Y', smoke_time) Day, count(1) Nb  "+
				"    FROM     "+ DATABASE_TABLE +" "+
				"    WHERE    strftime('%W', smoke_time) = strftime('%W', 'now') "+
				"    GROUP BY strftime('%d/%m/%Y', smoke_time) "+
				"    ORDER BY strftime('%d/%m/%Y', smoke_time) "+
				");";

			double res = -1;

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			if (mCursor != null) {
				mCursor.moveToFirst();
				res =  mCursor.getDouble(2);getAverage( mCursor.getDouble(0), mCursor.getDouble(1) );
			}

			return res;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}


	/**
	 * Return the number of cigarettes smoked each day of the week
	 */
	public ArrayList<SmokeCounter> getWeeklyDetails(){
		String sqlQuery =
			"SELECT   strftime('%w', smoke_time) Day, count(1) Nb  " +
			"FROM     "+ DATABASE_TABLE +" " +
			"WHERE    strftime('%W', smoke_time) = strftime('%W', 'now') " +
			"GROUP BY strftime('%w', smoke_time) " +
			"ORDER BY strftime('%Y-%m-%d', smoke_time); ";

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		return parseCursorAsSmokeCounter(mCursor);
	}


	/**
	 * Return the average number of cigarettes smoked by day for the last 6 weeks
	 */
	public ArrayList<SmokeAverage> getLastWeeksAverage(){
		String sqlQuery = 
			"SELECT Week, sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
			"FROM  "+
			"( "+
			"    SELECT   strftime('%W', smoke_time) Week, strftime('%Y-%m-%d', smoke_time) Day, count(1) Nb  "+
			"    FROM     "+ DATABASE_TABLE +" "+
			"    WHERE    strftime('%Y-%W', smoke_time) > strftime('%Y-%W', 'now', '-"+(NUMBER_OF_HISTORY*7)+" days') "+
			"    GROUP BY strftime('%W', smoke_time), strftime('%d/%m/%Y', smoke_time) "+ 
			") "+
			"GROUP BY Week " + 
			"ORDER BY Day; ";

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		return parseCursorAsSmokeAverage(mCursor);
	}


	/**
	 * Return the number of cigarettes smoked this month
	 */
	public int getMonthlyCount(){
		String sqlQuery = 
			"SELECT   count(1) Nb "+ 
			"FROM     "+ DATABASE_TABLE +" "+
			"WHERE    strftime('%m', smoke_time) = strftime('%m', 'now'); ";

		int res = -1;

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
			res = mCursor.getInt(0);
		}

		return res;
	}

	
	/**
	 * Return the number of cigarettes smoked this month
	 */
	public double getMonthlyCost(){
		String sqlQuery = 
			"SELECT   sum(ifnull(smoke_valid,0)) Nb "+
			"FROM     "+ DATABASE_TABLE +" "+
			"WHERE    strftime('%m', smoke_time) = strftime('%m', 'now'); ";

		double res = -1;

		Cursor mCursor = db.rawQuery(sqlQuery, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
			res = mCursor.getDouble(0);
		}

		return res;
	}


	/**
	 * Return the average number of cigarettes smoked by day this month
	 */
	public double getMonthlyAverage(){
		try {
			String sqlQuery = 
				"SELECT sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
				"FROM  "+
				"( "+
				"    SELECT   strftime('%d/%m/%Y', smoke_time) Day, count(1) Nb  "+
				"    FROM     "+ DATABASE_TABLE +" "+
				"    WHERE    strftime('%m', smoke_time) = strftime('%m', 'now') "+
				"    GROUP BY strftime('%d/%m/%Y', smoke_time) "+
				"    ORDER BY strftime('%d/%m/%Y', smoke_time) "+
				");";

			double res = -1;

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			if (mCursor != null) {
				mCursor.moveToFirst();
				res = mCursor.getDouble(2);//getAverage( mCursor.getDouble(0), mCursor.getDouble(1) );
			}

			return res;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	/**
	 * Return the number of cigarettes smoked by day for this month
	 */
	public ArrayList<SmokeCounter> getMonthlyDetails(){
		try {
			String sqlQuery = 
				"SELECT   strftime('%d/%m/%Y', smoke_time) Day, count(1) Nb, strftime('%d', smoke_time) Day, strftime('%m', smoke_time) Month, strftime('%Y', smoke_time) Year  "+ 
				"FROM     "+ DATABASE_TABLE +" "+ 
				"WHERE    strftime('%m', smoke_time) = strftime('%m', 'now') "+ 
				"GROUP BY strftime('%d/%m/%Y', smoke_time) "+ 
				"ORDER BY strftime('%d/%m/%Y', smoke_time); ";

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			return parseCursorAsSmokeCounter(mCursor);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}


	/**
	 * Return the average number of cigarettes smoked by day for the last 6 month
	 */
	public ArrayList<SmokeAverage> getLastMonthsAverage(){
		try {
			String sqlQuery = 
				"SELECT Month, sum(nb), count(nb), round(avg(nb), "+ROUNDING+") "+
				"FROM  "+
				"( "+
				"    SELECT   strftime('%m', smoke_time) Month, strftime('%Y-%m-%d', smoke_time) Day, count(1) Nb  "+
				"    FROM     "+ DATABASE_TABLE +" "+
				"    WHERE    strftime('%Y-%m', smoke_time) > strftime('%Y-%m', 'now', '-"+NUMBER_OF_HISTORY+" months') "+
				"    GROUP BY strftime('%m', smoke_time), strftime('%d/%m/%Y', smoke_time) ORDER BY smoke_time"+
				") "+
				"GROUP BY Month " +
				"ORDER BY Day; ";

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			return parseCursorAsSmokeAverage(mCursor);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}	

	/**
	 * Return the time since the last cigarette
	 */
	public SmokeTime getDelaySinceLast(){
		try {
			SmokeTime res = new SmokeTime();

			String sqlQuery = 
				"SELECT strftime('%s', datetime()),  strftime('%s', MAX(smoke_time))  "+
				"FROM "+ DATABASE_TABLE +";";

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			if (mCursor != null) {
				mCursor.moveToFirst();

				String now = mCursor.getString(0)+"000";
				String max = mCursor.getString(1)+"000";

				Calendar res1 = Calendar.getInstance();
				res1.setTimeZone(TimeZone.getDefault());
				res1.setTimeInMillis((new Long(now) - new Long(max)));
				res1.add(Calendar.HOUR, -1);

				res.setHour(res1.get(Calendar.HOUR_OF_DAY));
				res.setMinute(res1.get(Calendar.MINUTE));
				res.setSeconds(res1.get(Calendar.SECOND));
				res.genTotalMinutes();
			}

			return res;
		} catch (NumberFormatException e) {
			e.printStackTrace();
			return new SmokeTime();
		}
	}

	/**
	 * Return the average time between 2 cigarettes
	 */
	public SmokeTime getAverageDelay(){
		try {
			SmokeTime res = new SmokeTime();

			String sqlQuery = 
				"SELECT AVG(diff) "+
				"FROM ( "+
				"	    SELECT ( smoke_time - ( SELECT MAX(smoke_time) FROM "+ DATABASE_TABLE +" WHERE smoke_time < t1.smoke_time ) ) diff  "+
				"	    FROM "+ DATABASE_TABLE +" t1  "+
				"	    WHERE strftime('%m', smoke_time) = strftime('%m', 'now')  "+
				"	    ORDER BY smoke_time "+ 
				");";

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			if (mCursor != null) {
				mCursor.moveToFirst();
				res.setTotalTime(mCursor.getDouble(0));
				System.out.println("Delay : "+mCursor.getDouble(0));
				res.parse();
			}

			return res;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	/************************************************************************************/

	public void invalidDate(int _day, int _month, int _year) {
		String day = String.valueOf(_day);
		String month = String.valueOf(_month);
		String year = String.valueOf(_year);

		if ( day.length() == 1 )
			day = "0"+day;

		if ( month.length() == 1 )
			month = "0"+month;

		db.execSQL("DELETE FROM "+DATABASE_TABLE+" WHERE strftime('%Y-%m-%d', smoke_time) = strftime('%Y-%m-%d', '"+year+"-"+month+"-"+day+"');");        

	}

	public void initialLoad(){
		insertRandomData();
	}

	public void insertRandomData(){

		Calendar yesterday = Calendar.getInstance(); 
		yesterday.add(Calendar.DAY_OF_MONTH, -1); // Yesterday
		Calendar c = Calendar.getInstance(); // Today
		// MONTHS 
		for ( int i = -5 ; i <= 0 ; i ++ ){

			c = Calendar.getInstance(); // Today 
			c.add(Calendar.MONTH, i);
			int year = c.get(Calendar.YEAR); int month = c.get(Calendar.MONTH)+1;
			int lastDayOfMonth = getLastDayOfMonth(c.get(Calendar.MONTH));
			if ( month-1 == yesterday.get(Calendar.MONTH) )
				lastDayOfMonth = yesterday.get(Calendar.DAY_OF_MONTH);

			// DAYS 
			for ( int day = 1; day <= lastDayOfMonth ; day++ ){ // for each day of the month

				int nbInsertForCurrentDay = ((int) (Math.random()*10))+5; // between 5 and 15
				c.set(Calendar.DAY_OF_MONTH, day); 

				// OCCURENCY 
				for ( int x = 0 ; x < nbInsertForCurrentDay ; x++ ){
					int hour = ((int) (Math.random()*17))+6; // between 7 and 23 
					int minute = (int) (Math.random()*59); // between 1 and 59 
					int second = (int) (Math.random()*59); // between 1 and 59 
					int milisec = (int) (Math.random()*99); // between 1 and 99 // INSERT

					String sDay = day+"";
					if ( sDay.length() == 1 )
						sDay = "0"+day;

					String sMonth = month+"";
					if ( sMonth.length() == 1 )
						sMonth = "0"+sMonth;

					String sHour = hour+"";
					if ( sHour.length() == 1 )
						sHour = "0"+sHour;

					String sMinute = minute+"";
					if ( sMinute.length() == 1 )
						sMinute = "0"+sMinute;	

					String sSecond = second+"";
					if ( sSecond.length() == 1 )
						sSecond = "0"+sSecond;

					String sMiliSecond = milisec+"";
					if ( sMiliSecond.length() == 1 )
						sMiliSecond = "0"+sMiliSecond;
					if ( sMiliSecond.length() == 2 )
						sMiliSecond = "0"+sMiliSecond;

					db.execSQL("INSERT INTO smoke_table ( smoke_time, smoke_reason, smoke_valid ) VALUES ('"+year+"-"+sMonth+"-"+sDay+" "+sHour+":"+sMinute+":"+sSecond+"', 'Random', 'Y');");
				}
			}
		}

	}

	public static int getLastDayOfMonth( int month ){

		Calendar c = Calendar.getInstance(); 
		c.set(Calendar.MONTH, month+1); 
		c.set(Calendar.DAY_OF_MONTH, 1); 
		c.add(Calendar.DAY_OF_MONTH, -1);

		return c.get(Calendar.DAY_OF_MONTH);
	}

	
	public ArrayList<String> exportData(){
		
		ArrayList<String> data = new ArrayList<String>();
		
		data.add("Smoke Time"+CSV_SEPARATOR+"Smoke Reason"+CSV_SEPARATOR+"Smoke Price");

		try {
			String sqlQuery = 
				"SELECT smoke_time, smoke_reason, smoke_valid "+
				"FROM  "+ DATABASE_TABLE +"; ";

			Cursor mCursor = db.rawQuery(sqlQuery, null);

			if ( mCursor != null ){
				mCursor.moveToFirst();
				
				while (mCursor != null && ! mCursor.isAfterLast()) {
					String line = mCursor.getString(0)+ CSV_SEPARATOR +mCursor.getString(1)+ CSV_SEPARATOR +mCursor.getFloat(2);
					data.add(line);
					mCursor.moveToNext();
				}				
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return data;
	}

	/**
	 * Method used to store in DB all data contained in the ArrayList in param
	 * @param data : List of data in CSV format
	 */
	public void importData(ArrayList<String> data){
		for ( String line : data){

			String[] tmpValues = line.split(CSV_SEPARATOR);
			
			String sqlQuery = "INSERT INTO "+ DATABASE_TABLE +" () VALUES (null, '"+tmpValues[0]+"', '"+tmpValues[1]+"', '"+tmpValues[2]+"');";
	    	db.execSQL(sqlQuery);

		}
	}

}